﻿using KPIS.DBM;
using KPIS.GERP.GAMS.APP.App_UI.Commons;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.MODEL;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using Telerik.Web.UI; 

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class ACM_GENERATE_ACCOUNT_Auto : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        D_ACM_GENERATE_ACCOUNT_Auto auto = new D_ACM_GENERATE_ACCOUNT_Auto();
        D_ACM_MAS_BaseAccount bAcc = new D_ACM_MAS_BaseAccount();
        D_ACM_GENERAL_Ledger gl = new D_ACM_GENERAL_Ledger();
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadDDLBgmSourceSeq();
                LoadGridMaster();
                cmdPrint.Visible = false;
            }
        }

        protected void LoadDDLBgmSourceSeq()
        {
            ddlBgmSourceSeq.DataSource = source.GetList();
            ddlBgmSourceSeq.DataBind();

            ddlBgmSourceSeq.SelectedIndex = 0;
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            int nEmpSeq = Convert.ToInt32(Session["EmpSeq"]);
            int nCorrectCount = 0;
            int nCount = 0;
            string strResult = "";
            string strError = "";
            DataTable dtb;

            try
            {
                //INSERT Transaction_group case have new group
                foreach (GridDataItem data in grdACMAccountAuto.MasterTableView.GetSelectedItems())
                {
                    nCount++;
                    double dSumDr = 0;
                    double dSumCr = 0;
                    IList<M_ACM_GENERATE_ACCOUNT_Auto> infoDtlsList = auto.GetListDetail(main.ConvertNullInt(main.WebString(data["ref_seq"].Text)), main.WebString(data["ref_type"].Text));
                    foreach (M_ACM_GENERATE_ACCOUNT_Auto infoTemp in infoDtlsList)
                    {
                        if ((infoTemp.account_seq ?? 0) == 0)
                        {
                            strError += "เลขที่อ้างอิงที่ [" + main.WebString(data["ref_no"].Text) + "] ไม่สามารถระบุเลขที่บัญชีได้ครบถ้วน!!<br />";
                            break;
                        }

                        dSumDr += infoTemp.amount_dr ?? 0;
                        dSumCr += infoTemp.amount_cr ?? 0;
                    }


                    if (dSumDr.ToString() != dSumCr.ToString())
                    {
                        strError += "เลขที่อ้างอิงที่ [" + main.WebString(data["ref_no"].Text) + "] จำนวนเงินเดบิตกับเครดิตไม่เท่ากัน!!<br />";
                        break;
                    }

                    M_ACM_GENERAL_Ledger infoMain = new M_ACM_GENERAL_Ledger();
                    int snTranGrpSeq = gl.GetNextTranGrpSeq();
                    
                    infoMain.tran_type = main.WebString(data["tran_type"].Text);
                    infoMain.fiscal_period = main.GetFiscalPeriod(Convert.ToDateTime(main.WebString(data["tran_date"].Text)));
                    infoMain.tran_grp_seq = snTranGrpSeq;
                    infoMain.tran_code = gl.GetNextTranCode(main.WebString(data["bgm_source_seq"].Text), main.WebString(data["bgm_year"].Text), main.WebString(data["tran_type"].Text));
                    infoMain.tran_date = main.ConvertNullDateTime(main.WebString(data["tran_date"].Text));
                    infoMain.work_area_seq = main.ConvertNullInt(main.WebString(data["work_area_seq"].Text));
                    infoMain.bgm_source_seq = main.ConvertNullInt(main.WebString(data["bgm_source_seq"].Text));
                    infoMain.bgm_year = main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Month >= 10
                        ? main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Year + 544
                        : main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Year + 543;//main.ConvertNullInt(main.WebString(data["bgm_year"].Text));
                    infoMain.ref_no = main.WebString(data["ref_no"].Text);
                    infoMain.tran_name = main.WebString(data["tran_name"].Text);
                    infoMain.remark = null;
                    infoMain.record_status = "W";
                    infoMain.created_by = nEmpSeq;
                    infoMain.created_when = DateTime.Now;

                    string strTranSeq = gl.Insert_Transaction(infoMain, true);

                    if (string.IsNullOrEmpty(strTranSeq))
                    {
                        return;
                    }
                    
                    foreach (M_ACM_GENERATE_ACCOUNT_Auto infoTemp in infoDtlsList)
                    {
                        M_ACM_GENERAL_Ledger infoDtls = new M_ACM_GENERAL_Ledger();
                        infoDtls.tran_seq = Convert.ToInt32(strTranSeq);
                        infoDtls.account_seq = infoTemp.account_seq;
                        infoDtls.dr_or_cr = infoTemp.dr_or_cr;
                        infoDtls.amount = (infoTemp.dr_or_cr == "Dr" ? infoTemp.amount_dr : infoTemp.amount_cr);
                        infoDtls.record_status = "W";
                        infoDtls.created_by = nEmpSeq;
                        infoDtls.created_when = DateTime.Now;

                        gl.Insert_DB_CR(infoDtls);
                    }

                    #region Transfer
                    //if (infoMain.ref_no.StartsWith("TF"))
                    //{
                    //    DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());

                    //    string strSQL = "select facc.bgm_source_seq as f_bgm_source_seq, tacc.bgm_source_seq as t_bgm_source_seq"
                    //        + ", facc.account_seq as f_account_seq, tacc.account_seq as t_account_seq"
                    //        + ", ba.cross_budget_income_account_seq, ba.cross_budget_expense_account_seq"
                    //        + " from fim_transfers tf"
                    //        + " left join fim_mas_bank_accounts facc on tf.from_bank_acc_seq = facc.bank_acc_seq"
                    //        + " left join fim_mas_bank_accounts tacc on tf.to_bank_acc_seq = tacc.bank_acc_seq"
                    //        + " cross join acm_mas_base_account ba"
                    //        + " where tf.transfer_seq = @transfer_seq";

                    //    try
                    //    {
                    //        dbManager.Open();
                    //        dbManager.CreateParameters(1);
                    //        dbManager.AddParameters(0, "@transfer_seq", data["ref_seq"].Text);
                    //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    //    }
                    //    catch (Exception ex)
                    //    {
                    //        throw (ex);
                    //    }
                    //    finally
                    //    {
                    //        dbManager.Dispose();
                    //    }


                    //    if (dtb.Rows.Count > 0 && dtb.Rows[0]["f_bgm_source_seq"].ToString() != dtb.Rows[0]["t_bgm_source_seq"].ToString())
                    //    {
                    //        infoMain.tran_type = main.WebString(data["tran_type"].Text);
                    //        infoMain.fiscal_period = main.GetFiscalPeriod(Convert.ToDateTime(main.WebString(data["tran_date"].Text)));
                    //        infoMain.tran_grp_seq = snTranGrpSeq;
                    //        infoMain.tran_code = gl.GetNextTranCode(main.WebString(data["bgm_source_seq"].Text), main.WebString(data["bgm_year"].Text), main.WebString(data["tran_type"].Text));
                    //        infoMain.tran_date = main.ConvertNullDateTime(main.WebString(data["tran_date"].Text));
                    //        infoMain.work_area_seq = main.ConvertNullInt(main.WebString(data["work_area_seq"].Text));
                    //        infoMain.bgm_source_seq = main.ConvertNullInt(dtb.Rows[0]["t_bgm_source_seq"]);
                    //        infoMain.bgm_year = main.ConvertNullInt(main.WebString(data["bgm_year"].Text));
                    //        infoMain.ref_no = main.WebString(data["ref_no"].Text);
                    //        infoMain.tran_name = main.WebString(data["tran_name"].Text);
                    //        infoMain.remark = null;
                    //        infoMain.record_status = "W";
                    //        infoMain.created_by = nEmpSeq;
                    //        infoMain.created_when = DateTime.Now;

                    //        strTranSeq = gl.Insert_Transaction(infoMain, false);

                    //        M_ACM_GENERAL_Ledger infoDtls = new M_ACM_GENERAL_Ledger();
                    //        infoDtls.tran_seq = Convert.ToInt32(strTranSeq);
                    //        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["t_account_seq"]);
                    //        infoDtls.dr_or_cr = "Dr";
                    //        infoDtls.amount = infoDtlsList[0].amount_dr;
                    //        infoDtls.record_status = "W";
                    //        infoDtls.created_by = nEmpSeq;
                    //        infoDtls.created_when = DateTime.Now;

                    //        gl.Insert_DB_CR(infoDtls);

                    //        infoDtls = new M_ACM_GENERAL_Ledger();
                    //        infoDtls.tran_seq = Convert.ToInt32(strTranSeq);
                    //        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["cross_budget_income_account_seq"]);
                    //        infoDtls.dr_or_cr = "Cr";
                    //        infoDtls.amount = infoDtlsList[0].amount_dr;
                    //        infoDtls.record_status = "W";
                    //        infoDtls.created_by = nEmpSeq;
                    //        infoDtls.created_when = DateTime.Now;

                    //        gl.Insert_DB_CR(infoDtls);
                    //    }
                    //}
                    #endregion

                    #region fim_vat
                    if (data["ref_type"].Text == "fim_vat" && main.WebString(data["bgm_source_seq"].Text) == "11")
                    {
                        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());

                        string strSQL = @"
select sum(coalesce(vat_buy_amount, 0)) vat_buy_amount
, ba.buy_vat_account_seq, ba.cross_account_seq
from fim_vat_dtls vd
left join fim_payments p on vd.pay_seq = p.pay_seq 
left join fim_money_return_dtls rt on vd.return_dtls_seq = rt.return_dtls_seq
left join fim_money_returns r on rt.return_seq = r.return_seq
left join fim_money_requests mr on r.money_req_seq = mr.money_req_seq
cross join acm_mas_base_account ba
where vd.vat_seq = @vat_seq
and (p.bgm_source_seq = 1 or mr.bgm_source_seq = 1)
and (p.pay_seq is not null or rt.return_dtls_seq is not null)
group by ba.buy_vat_account_seq, ba.cross_account_seq
";
                        try
                        {
                            dbManager.Open();
                            dbManager.CreateParameters(1);
                            dbManager.AddParameters(0, "@vat_seq", data["ref_seq"].Text);
                            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                        }
                        catch (Exception ex)
                        {
                            throw (ex);
                        }
                        finally
                        {
                            dbManager.Dispose();
                        }

                        infoMain.tran_type = main.WebString(data["tran_type"].Text);
                        infoMain.fiscal_period = main.GetFiscalPeriod(Convert.ToDateTime(main.WebString(data["tran_date"].Text)));
                        infoMain.tran_grp_seq = snTranGrpSeq;
                        infoMain.tran_code = gl.GetNextTranCode("1", main.WebString(data["bgm_year"].Text), main.WebString(data["tran_type"].Text));
                        infoMain.tran_date = main.ConvertNullDateTime(main.WebString(data["tran_date"].Text));
                        infoMain.work_area_seq = main.ConvertNullInt(main.WebString(data["work_area_seq"].Text));
                        infoMain.bgm_source_seq = 1;
                        infoMain.bgm_year = main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Month >= 10
                            ? main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Year + 544
                            : main.ConvertNullDateTime(main.WebString(data["tran_date"].Text)).Value.Year + 543;//main.ConvertNullInt(main.WebString(data["bgm_year"].Text));
                        infoMain.ref_no = main.WebString(data["ref_no"].Text);
                        infoMain.tran_name = main.WebString(data["tran_name"].Text);
                        infoMain.remark = null;
                        infoMain.record_status = "W";
                        infoMain.created_by = nEmpSeq;
                        infoMain.created_when = DateTime.Now;

                        string strTranSeq2 = gl.Insert_Transaction(infoMain, false);

                        M_ACM_GENERAL_Ledger infoDtls = new M_ACM_GENERAL_Ledger();
                        //TranSeq1
                        infoDtls.tran_seq = Convert.ToInt32(strTranSeq);
                        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["buy_vat_account_seq"]);
                        infoDtls.dr_or_cr = "Dr";
                        infoDtls.amount = main.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"]);
                        infoDtls.record_status = "W";
                        infoDtls.created_by = nEmpSeq;
                        infoDtls.created_when = DateTime.Now;

                        gl.Insert_DB_CR(infoDtls);

                        infoDtls = new M_ACM_GENERAL_Ledger();
                        infoDtls.tran_seq = Convert.ToInt32(strTranSeq);
                        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["cross_account_seq"]);
                        infoDtls.dr_or_cr = "Cr";
                        infoDtls.amount = main.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"]);
                        infoDtls.record_status = "W";
                        infoDtls.created_by = nEmpSeq;
                        infoDtls.created_when = DateTime.Now;

                        gl.Insert_DB_CR(infoDtls);

                        //TranSeq2
                        infoDtls.tran_seq = Convert.ToInt32(strTranSeq2);
                        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["cross_account_seq"]);
                        infoDtls.dr_or_cr = "Dr";
                        infoDtls.amount = main.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"]);
                        infoDtls.record_status = "W";
                        infoDtls.created_by = nEmpSeq;
                        infoDtls.created_when = DateTime.Now;

                        gl.Insert_DB_CR(infoDtls);

                        infoDtls = new M_ACM_GENERAL_Ledger();
                        infoDtls.tran_seq = Convert.ToInt32(strTranSeq2);
                        infoDtls.account_seq = main.ConvertNullInt(dtb.Rows[0]["buy_vat_account_seq"]);
                        infoDtls.dr_or_cr = "Cr";
                        infoDtls.amount = main.ConvertNullDouble(dtb.Rows[0]["vat_buy_amount"]);
                        infoDtls.record_status = "W";
                        infoDtls.created_by = nEmpSeq;
                        infoDtls.created_when = DateTime.Now;

                        gl.Insert_DB_CR(infoDtls);
                    }
                    #endregion

                    auto.UpdateRefType(main.WebString(data["ref_type"].Text), Convert.ToInt32(main.WebString(data["ref_seq"].Text)), infoMain.tran_grp_seq.Value);
                    nCorrectCount++;
                }

                strResult += "ลงบัญชีได้ทั้งหมด " + nCorrectCount.ToString("#,##0") + " จากทั้งหมด " + nCount.ToString("#,##0") + " รายการ";
                lblResult.Text = strError + strResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                LoadGridMaster();
            }
        }

        protected void grdACMAccountAuto_ItemCommand(object source, GridCommandEventArgs e)
        {
            if (e.CommandName == "Filter" || e.CommandName == "Sort" || e.CommandName == "Page")
            {
                LoadGridMaster();
            }
        }

        protected void LoadGridMaster()
        {
            string strBgmSourceSeq = ddlBgmSourceSeq.SelectedValue;
            string WorkAreaSeq = "1";
            string strStartRefNo = txtStartRefNo.Text;
            string strEndRefNo = txtEndRefNo.Text;
            var data = auto.GetListMaster(strBgmSourceSeq, WorkAreaSeq, rblTranType.SelectedValue) as List<M_ACM_GENERATE_ACCOUNT_Auto>;

            if (!string.IsNullOrEmpty(strStartRefNo) && !string.IsNullOrEmpty(strEndRefNo))
            {
                int? nStartRefNo = main.ConvertNullInt(strStartRefNo.Substring(2));
                int? nEndRefNo = main.ConvertNullInt(strEndRefNo.Substring(2));
                var temp = new M_ACM_GENERATE_ACCOUNT_Auto[data.Count];
                data.CopyTo(temp);

                foreach (var item in temp)
                {
                    string strRefNo = item.ref_no.Substring(2);
                    int? nRefNo = main.ConvertNullInt(strRefNo);

                    if (nRefNo.HasValue && nRefNo >= nStartRefNo && nRefNo <= nEndRefNo)
                    {
                        
                    }
                    else
                    {
                        data.Remove(item);
                    }
                }
            }

            foreach (var item in data)
            {
                string strRefSeq = item.ref_seq.ToString();
                string strRefType = item.ref_type;
                item.amount = auto.GetListDetail(main.ConvertNullInt(strRefSeq), strRefType).Where(m => m.dr_or_cr == "Dr").Sum(m => m.amount_dr);
            }
             
            grdACMAccountAuto.DataSource = data;
            grdACMAccountAuto.DataBind();
        }

        protected void RadGrid1_DetailTableDataBind(object sender, GridDetailTableDataBindEventArgs e)
        {
            GridDataItem dataItem = (GridDataItem)e.DetailTableView.ParentItem;
            string strRefSeq = main.WebString(dataItem["ref_seq"].Text);
            string strRefType = main.WebString(dataItem["ref_type"].Text);
            e.DetailTableView.DataSource = auto.GetListDetail(main.ConvertNullInt(strRefSeq), strRefType);
        }

        //protected void ddlBgmSourceSeq_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        //{
        //    LoadGridMaster();
        //}

        protected void cmdFilter_Click(object sender, EventArgs e)
        {
            LoadGridMaster();
            cmdPrint.Visible = rblTranType.SelectedValue == "fim_receives";
        }

        protected void cmdPrint_Click(object sender, EventArgs e)
        {
            string strBgmSourceSeq = ddlBgmSourceSeq.SelectedValue;
            string WorkAreaSeq = "1";
            string strStartRefNo = txtStartRefNo.Text;
            string strEndRefNo = txtEndRefNo.Text;
            var list = auto.GetListMaster(strBgmSourceSeq, WorkAreaSeq, rblTranType.SelectedValue) as List<M_ACM_GENERATE_ACCOUNT_Auto>;

            if (!string.IsNullOrEmpty(strStartRefNo) && !string.IsNullOrEmpty(strEndRefNo))
            {
                int? nStartRefNo = main.ConvertNullInt(strStartRefNo.Substring(2));
                int? nEndRefNo = main.ConvertNullInt(strEndRefNo.Substring(2));
                var temp = new M_ACM_GENERATE_ACCOUNT_Auto[list.Count];
                list.CopyTo(temp);

                foreach (var item in temp)
                {
                    string strRefNo = item.ref_no.Substring(2);
                    int? nRefNo = main.ConvertNullInt(strRefNo);

                    if (nRefNo.HasValue && nRefNo >= nStartRefNo && nRefNo <= nEndRefNo)
                    {

                    }
                    else
                    {
                        list.Remove(item);
                    }
                }
            }

            MailMerge mm = new MailMerge();
            var dic = new Dictionary<string, string>();
            int row = 2;

            foreach(var item in list)
            {
                dic.Add("A" + row, item.ref_no + " " + item.tran_name);
                dic.Add("B" + row, item.tran_date.HasValue ? item.tran_date.Value.ToShortDateString() : "-");

                row++;
                var dtls = auto.GetListDetail(item.ref_seq, item.ref_type);

                foreach(var dtl in dtls)
                {
                    dic.Add("A" + row, dtl.account_name);
                    dic.Add("B" + row, dtl.account_code);
                    dic.Add("C" + row, dtl.amount_dr.HasValue ? dtl.amount_dr.Value.ToString("#,##0.00") : "");
                    dic.Add("D" + row, dtl.amount_cr.HasValue ? dtl.amount_cr.Value.ToString("#,##0.00") : "");
                    row++;
                }

                row++;
            }

            mm.MergeExcel(Response, Server.MapPath("~/Reports/Templates/Receive.xlsx"), string.Format("Receive{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
        }
    }
}
